unit clsSupervisor;

interface

uses Dm, dbtables, biblio, SysUtils;

type
  TSupervisor = class(TObject)
  private
    Sup_Codigo: string;
  public
    Cid_Codigo: string;
    Ban_Codigo: string;
    Sup_nome: string;
    Sup_razao: string;
    Sup_responsavel: string;
    Sup_Endereco: string;
    Sup_Bairro: string;
    Sup_Fone: string;
    Sup_Cep: string;
    Sup_NumConta: string;
    Sup_Agencia: string;
    Sup_Comissao: Double;
    Sup_Cnpj: string;
    Sup_Cpf: string;
    Sup_Inscest: string;
    Sup_Dataina: TDateTime;
    Sup_rg: string;
    Sup_Datacad: TDateTime;
    Sup_SaldoInicial: Double;
    Sup_Observacao: string;
    Sup_Celular: string;
    Sup_Email: string;
    ent_codigo: string;
    hist_codigo: string;
    function Gravar(p_Sup_Codigo: string): Byte;
    function Recuperar(p_Sup_Codigo: string): Byte;
    function Recuperar_ultimo: string;
    function Recuperar_primeiro: string;
    function Excluir(p_Sup_Codigo: string): Byte;
    procedure Consultar(var p_q: TQuery; p_nome: string);
    function RetornaPriUlt(priUlt: Byte): string;
  end;

implementation

function TSupervisor.Gravar;
var
  tq: TQuery;
begin
  tq := Tquery.Create(DmInt);
  tq.databasename := dmint.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('SELECT * FROM Supervisor where Sup_Codigo=:k_Sup_Codigo');
  tq.parambyname('k_Sup_Codigo').asstring := strzero(p_Sup_Codigo, 3);
  tq.open;
  if tq.eof then begin
    tq.sql.clear;
    tq.params.clear;
    tq.sql.add('INSERT INTO Supervisor VALUES(:k_Sup_Codigo,');
    tq.sql.add(':k_Cid_Codigo,');
    if Ban_Codigo <> '' then tq.sql.add(':k_Ban_Codigo,') else tq.sql.add('null,');
    tq.sql.add(':k_Sup_nome,' +
      ':k_Sup_responsavel,' +
      ':k_Sup_Endereco,' +
      ':k_Sup_Bairro,' +
      ':k_Sup_Fone,' +
      ':k_Sup_Cep,' +
      ':k_Sup_NumConta,' +
      ':k_Sup_Agencia,' +
      ':k_Sup_Comissao,' +
      ':k_Sup_Cnpj,' +
      ':k_Sup_Cpf,' +
      ':k_Sup_Inscest,' +
      ':k_Sup_Dataina,' +
      ':k_Sup_rg,' +
      ':k_Sup_Datacad,' +
      ':k_Sup_SaldoInicial,' +
      ':k_Sup_Observacao,' +
      ':k_Sup_Celular,' +
      ':k_Sup_Email');
    if ent_Codigo <> '' then tq.sql.add(',:k_ent_Codigo') else tq.sql.add(',null');
    if hist_codigo <> '' then tq.sql.add(',:k_hist_Codigo') else tq.sql.add(',null');
    tq.sql.add(',:K_Sup_razao)');
  end else begin
    tq.sql.clear;
    tq.params.clear;
    tq.sql.add('UPDATE Supervisor SET ');
    tq.sql.add('Cid_Codigo=:k_Cid_Codigo,');
    if Ban_Codigo <> '' then tq.sql.add('Ban_Codigo=:k_Ban_codigo,') else tq.sql.add('Ban_Codigo=null,');
    tq.sql.add('Sup_nome=:k_Sup_nome,' +
      'Sup_Razao=:k_Sup_razao,' +
      'Sup_Responsavel=:k_Sup_responsavel,' +
      'Sup_Endereco=:k_Sup_Endereco,' +
      'Sup_Bairro=:k_Sup_Bairro,' +
      'Sup_fone=:k_Sup_Fone,' +
      'Sup_Cep=:k_Sup_Cep,' +
      'Sup_NumConta=:k_Sup_NumConta,' +
      'Sup_Agencia=:k_Sup_Agencia,' +
      'Sup_Comissao=:k_Sup_Comissao,' +
      'Sup_Cnpj=:k_Sup_Cnpj,' +
      'Sup_Cpf=:k_Sup_Cpf,' +
      'Sup_Inscest=:k_Sup_Inscest,' +
      'Sup_Dataina=:k_Sup_Dataina,' +
      'Sup_Rg=:k_Sup_rg,' +
      'Sup_Datacad=:k_Sup_Datacad,' +
      'Sup_SaldoInicial=:k_Sup_SaldoInicial,' +
      'Sup_Observacao=:k_Sup_Observacao,' +
      'Sup_Celular=:k_Sup_Celular,' +
      'Sup_Email=:k_Sup_Email ');
    if ent_Codigo <> '' then tq.sql.add(',ent_codigo=:k_ent_Codigo ') else tq.sql.add(',ent_codigo=null ');
    if hist_codigo <> '' then tq.sql.add(',hist_codigo=:k_hist_Codigo ') else tq.sql.add(',hist_codigo=null ');
    tq.sql.add(' Where Sup_Codigo=:k_Sup_Codigo');
  end;
  tq.parambyname('k_Sup_Codigo').asstring := strzero(p_Sup_Codigo, 3);
  tq.parambyname('k_Cid_Codigo').asstring := strzero(Cid_Codigo, 4);
  if Ban_Codigo <> '' then tq.ParamByName('k_Ban_Codigo').asstring := strzero(Ban_codigo, 3);
  tq.parambyname('k_Sup_Nome').asstring := Sup_nome;
  tq.ParamByName('K_Sup_Razao').AsString := Sup_razao;
  tq.parambyname('k_Sup_responsavel').asstring := Sup_Responsavel;
  tq.parambyname('k_Sup_endereco').asstring := sup_endereco;
  tq.parambyname('k_Sup_bairro').asstring := Sup_Bairro;
  tq.parambyname('k_Sup_fone').asstring := Sup_Fone;
  tq.parambyname('k_Sup_cep').asstring := Sup_Cep;
  tq.parambyname('k_Sup_Numconta').asstring := Sup_NumConta;
  tq.parambyname('k_Sup_Agencia').asstring := Sup_Agencia;
  tq.parambyname('k_Sup_Comissao').asfloat := Sup_Comissao;
  tq.parambyname('k_Sup_Cnpj').asstring := Sup_Cnpj;
  tq.parambyname('k_Sup_Cpf').asstring := Sup_Cpf;
  tq.parambyname('k_Sup_Inscest').asstring := Sup_Inscest;
  tq.parambyname('k_Sup_DataIna').asdatetime := Sup_Dataina;
  tq.parambyname('k_Sup_Rg').asstring := Sup_Rg;
  tq.parambyname('k_Sup_DataCad').asdatetime := Sup_DataCad;
  tq.parambyname('k_Sup_SaldoInicial').asfloat := Sup_SaldoInicial;
  if Sup_Observacao = '' then Sup_Observacao := ' '; // Postgres
  tq.parambyname('k_Sup_Observacao').AsBlob := Sup_Observacao;
  tq.parambyname('k_Sup_Celular').asstring := Sup_Celular;
  tq.parambyname('k_Sup_email').asstring := Sup_Email;
  if ent_Codigo <> '' then tq.parambyname('k_ent_Codigo').asstring := strzero(ent_Codigo, 5);
  if hist_Codigo <> '' then tq.parambyname('k_hist_Codigo').asstring := hist_codigo;
  tq.close;
  tq.execsql;
  result := 1;
  tq.close;
  tq.Free;
end;

function TSupervisor.Recuperar;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.sql.add('SELECT * FROM Supervisor where Sup_Codigo=:k_Sup_Codigo');
  tq.Params[0].asstring := strzero(p_Sup_Codigo, 3);
  tq.open;
  Sup_Codigo := p_Sup_Codigo;
  if tq.eof then begin
    Cid_Codigo := '';
    Ban_codigo := '';
    Sup_nome := '';
    Sup_razao := '';
    Sup_responsavel := '';
    Sup_Endereco := '';
    Sup_Bairro := '';
    Sup_Fone := '';
    Sup_Cep := '';
    Sup_NumConta := '';
    Sup_Agencia := '';
    Sup_Comissao := 0;
    Sup_Cnpj := '';
    Sup_Cpf := '';
    Sup_Inscest := '';
    Sup_Dataina := 0;
    Sup_rg := '';
    Sup_Datacad := date;
    Sup_SaldoInicial := 0;
    Sup_Observacao := '';
    Sup_Celular := '';
    Sup_Email := '';
    ent_codigo := '';
    hist_codigo := '';
    Result := 0;
  end else begin
    Cid_Codigo := tq.fieldbyname('Cid_Codigo').asstring;
    Ban_codigo := tq.fieldbyname('Ban_Codigo').asstring;
    Sup_nome := tq.fieldbyname('Sup_nome').asstring;
    Sup_razao := tq.fieldbyname('Sup_razao').asstring;
    Sup_responsavel := tq.fieldbyname('Sup_Responsavel').asstring;
    Sup_Endereco := tq.fieldbyname('Sup_Endereco').asstring;
    Sup_Bairro := tq.fieldbyname('Sup_Bairro').asstring;
    Sup_Fone := tq.fieldbyname('Sup_Fone').asstring;
    Sup_Cep := tq.fieldbyname('Sup_cep').asstring;
    Sup_NumConta := tq.fieldbyname('Sup_NumConta').asstring;
    Sup_Agencia := tq.fieldbyname('Sup_Agencia').asstring;
    Sup_Comissao := tq.fieldbyname('Sup_Comissao').asfloat;
    Sup_Cnpj := tq.fieldbyname('Sup_Cnpj').asstring;
    Sup_Cpf := tq.fieldbyname('Sup_Cpf').asstring;
    Sup_Inscest := tq.fieldbyname('Sup_Inscest').asstring;
    Sup_Dataina := tq.fieldbyname('Sup_Dataina').asdatetime;
    Sup_rg := tq.fieldbyname('Sup_Rg').asstring;
    Sup_Datacad := tq.fieldbyname('Sup_Datacad').asdatetime;
    Sup_SaldoInicial := tq.fieldbyname('Sup_SaldoInicial').asfloat;
    Sup_Observacao := tq.fieldbyname('Sup_Observacao').asstring;
    Sup_Celular := tq.fieldbyname('Sup_Celular').asstring;
    Sup_Email := tq.fieldbyname('Sup_Email').asstring;
    ent_codigo := tq.fieldbyname('ent_codigo').asstring;
    hist_codigo := tq.fieldbyname('hist_codigo').asstring;
    Result := 1;
  end;
  tq.Close;
  tq.Free;
end;

function TSupervisor.Excluir;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := dmint.db.databasename;
  tq.active := False;
  tq.close;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('DELETE FROM Supervisor WHERE Sup_Codigo=:k_Sup_Codigo');
  tq.parambyname('k_Sup_Codigo').asstring := strzero(p_Sup_Codigo, 3);
  tq.execSQL;
  Result := 1;
  tq.close;
  tq.Free;
end;

procedure TSupervisor.Consultar;
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('Select * from Supervisor where Sup_Nome>=:k_Sup_Nome order by Sup_Nome');
  p_q.parambyname('k_Sup_Nome').asstring := p_nome;
end;

function TSupervisor.Recuperar_ultimo: string;
var
  tq: TQuery;
begin
  tq := TQuery.create(DmInt.Db);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.Active := false;
  tq.sql.clear;
  tq.Params.clear;
  tq.SQL.add('Select max(sup_codigo) as maior from supervisor');
  tq.open;
  if tq.fieldbyname('maior').AsString <> '' then
    Result := inttostr(strtoint(tq.fieldbyname('maior').Asstring) + 1)
  else
    Result := '1';
  tq.close;
  tq.free;
end;

function TSupervisor.Recuperar_primeiro;
var
  tq: TQuery;
begin
  tq := TQuery.create(DmInt.Db);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.Active := false;
  tq.sql.clear;
  tq.Params.clear;
  tq.SQL.add('Select min(sup_codigo) as menor from supervisor');
  tq.open;
  if tq.fieldbyname('menor').AsString <> '' then
    Result := tq.fieldbyname('menor').Asstring
  else
    Result := '0';
  tq.close;
  tq.free;
end;

function TSupervisor.RetornaPriUlt(priUlt: Byte): string;
var
  tq: TQuery;
begin
  tq := TQuery.create(DmInt.Db);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.Active := false;
  tq.sql.clear;
  tq.Params.clear;
  if priUlt = 0 then begin
    tq.SQL.add('Select min(sup_codigo) as tot from supervisor');
  end else begin
    tq.SQL.add('Select max(sup_codigo) as tot from supervisor');
  end;
  tq.open;
  if tq.fieldbyname('tot').AsString <> '' then
    Result := tq.fieldbyname('tot').Asstring
  else
    Result := '';
  tq.close;
  tq.free;
end;

end.
